package common.store.web.dao;

import common.store.web.bean.Product;
import common.store.common.util.C3p0Pool;
import common.store.web.bean.Supplier;
import common.store.web.bean.User;
import org.apache.commons.beanutils.converters.SqlDateConverter;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * 商品的数据库操作
 */
public class ProductDao {

    private static QueryRunner qr;

    static {
        qr = new QueryRunner(C3p0Pool.getDataSource());
    }




    /**
     * 获取数据库中商品的数量
     * @return
     */
    public Integer getProductCount() {
        String sql = "select count(*) from product";

        try {
            Long count = (Long)qr.query(sql, new ScalarHandler());
            return count.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * #  页数            索引   数量
     *#  1               0      10
     *#  2               10     10
     *#  3               20     10
     *#索引 = (页数 - 1) * 数量
     */
    public List<Product> getProductListByCurrentPageAndMaxCountAndCid(Integer currentPage, Integer maxCount, String cid) {

        String sql = "select p.pid , p.pname , p.pimage , p.shop_price as shopPrice from product p where 1=1 ";

        List<Object> param = new ArrayList<>();
        //判断cid是否存在，存在则进行拼接操作
        if(null != cid && !cid.equals("")){
            sql += " and cid = ? ";
            param.add(cid);
        }
        sql += "LIMIT ? ,?";
        param.add((currentPage - 1) * maxCount);
        param.add(maxCount);
        try {
            return qr.query(sql, new BeanListHandler<>(Product.class) , param.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.emptyList();
    }


    public Product getProductByPid(String pid) {
        String sql = "select p.pid  , p.pname , s.sname, p.pimage , p.shop_price as shopPrice , p.market_price marketPrice , p.pdesc from product p ,supplier s where p.s_id=s.s_id and p.pid = ?";

        try {
            return qr.query(sql,new BeanHandler<>(Product.class),pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public List<Product> getProductListByWord(String word) {

        String sql = "select p.pname , p.pid from product p where p.pname like ? LIMIT 5";

        try {
            return qr.query(sql, new BeanListHandler<>(Product.class), "%" + word + "%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * 根据用户选择的数量和Pid加入购物车
     * @param product
     * @return
     */

    public  Integer addCartByPid(Product product) {

        if(!isEmpty(product.getUid(),product.getPid()))//如果没有添加过
        {
            try {
                return qr.update("insert into t_cart(uid,pid,count) values(? ,?,?)" ,product.getUid()  , product.getPid(),product.getQuantity());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        else
        {
           String sql="update t_cart set count=count+? where uid=? and pid=?";
            try {
                return qr.update(sql,product.getQuantity(),product.getUid(),product.getPid());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        return 0;
    }

    /**
     * 判断购物车里是否添加过
     * @param uid
     * @param pid
     * @return
     */
    public boolean isEmpty(String uid,String pid)
    {
        String  sql="select count(c.id) from t_cart as c where c.uid=? and c.pid=? ";
        try {
            Long rows=(Long)qr.query(sql,new ScalarHandler(),uid,pid);
            if(rows!=0)
            {
                return true;//存在
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return false;//不存在该商品
    }

    /**
     *  根据用户uid找出购物车商品
     * @param uid
     * @return
     */
    public List<Product> getProductsByuser(String uid) {
        String sql="SELECT p.pimage ,p.pname ,p.market_price as marketPrice,c.count as quantity  from product as p  , t_cart as c where p.pid = c.pid and c.uid=? ";

        try {
            return qr.query(sql,new BeanListHandler<>(Product.class),uid);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Collections.EMPTY_LIST;
    }

    /**
     * 根据分类主键查询下面的商品列表
     * @param cid
     * @return
     */
    public Integer getProductCountByCid(String cid) {
        String sql = "select count(*) from product where 1=1";
        List<Object> param = new ArrayList<>();
        //判断cid是否存在，存在则进行拼接操作
        if(null != cid && !cid.equals("")){
            sql += " and cid = ? ";
            param.add(cid);
        }
        try {
            Long count = (Long)qr.query(sql, new ScalarHandler() , param.toArray());
            return count.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public List<Product> getHotProduct() {

            try {
                return qr.query("select p.pid , p.pimage ,p.pname ,p.market_price as marketPrice from product p where p.is_hot=1 limit 9" , new BeanListHandler<>(Product.class));
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return Collections.EMPTY_LIST;
        }
    }

